package com.fxl.majiang.util;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 获取Excel 当前列的值
     * @param cell
     * @return val
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()){
            case NUMERIC: //日期
                cellValue = stringDateProcess(cell);
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        cellValue = StringUtil.getString(cellValue);
        return cellValue;
    }

    /**
     * 处理日期格式
     * @param cell
     * @return
     */
    public static String stringDateProcess(Cell cell){
        String result = null;
        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm");
            } else {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = DateUtil
                    .getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }

        return result;
    }

    /**
     * 导出07版excel （带水印）
     * @param titleList 表格total信息 (key-标题名称, value-对应字段名)
     * @param dataList  导出的数据 （key-字段名，value-值）
     * @param response
     * @throws IOException
     */
    public static void exportExcelSxssf(String fileName, List<HashMap<String, String>> titleList, List<HashMap<String, Object>> dataList, HttpServletResponse response) throws IOException {

        //创建工作簿
        SXSSFWorkbook wb = new SXSSFWorkbook(5000);

        //创建工作表
        Sheet sheet = wb.createSheet(fileName);

        //在第1行创建rows  (表标题)
        Row title = sheet.createRow(0);
        title.setHeightInPoints(30);//行高

        CellStyle titleStyle = ExcelUtil.getBiaoTouStyleSxssf(wb, false, false, (short)0);

        // 构建标题行
        for(int i=0; i<titleList.size(); i++){
            Cell cell = title.createCell(i);
            cell.setCellValue(titleList.get(i).get("title"));
            cell.setCellStyle(titleStyle);
        }

        CellStyle dygStyle = ExcelUtil.getDygStyleSxssf(wb);

        for(int i=0; i<dataList.size(); i++){
            Map<String, Object> data = dataList.get(i);
            Row row = sheet.createRow(i + 1);

            for(int j=0; j<titleList.size(); j++){
                Cell cell = row.createCell(j);
                cell.setCellValue(ObjectUtils.getDisplayString(data.get(titleList.get(j).get("key"))));
                cell.setCellStyle(dygStyle);
            }
        }

        OutputStream outputStream = null;
        //导出数据
        try {
            //设置Http响应头告诉浏览器下载这个附件
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");

            outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage());
        }finally {
            if(outputStream != null){
                outputStream.close();
            }

            wb.dispose();
        }
    }

    /**
     * 获取表头样式
     * 表头不加粗，居中显示
     * @param wb       工作簿
     * @param isBold   是否加粗
     * @param isBjs    是否背景色
     * @param color    背景色颜色
     * @return
     */
    public static CellStyle getBiaoTouStyleSxssf(SXSSFWorkbook wb, boolean isBold, boolean isBjs, short color){
        CellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HorizontalAlignment.CENTER);//居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        //设置字体
        Font font2 = wb.createFont();
        font2.setFontHeightInPoints((short) 11);//字体大小
        font2.setFontName("宋体");//字体
        if(isBold){
            font2.setBold(true);//粗体显示
        }
        style.setFont(font2);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        //自动换行
        style.setWrapText(true);

        if(isBjs){
            //设置背景色
            style.setFillForegroundColor(color);
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        return style;
    }

    /**
     * 获取单元格样式
     * 不加粗左对齐
     * @param wb
     * @return
     */
    public static CellStyle getDygStyleSxssf(SXSSFWorkbook wb){
        CellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HorizontalAlignment.LEFT);//左对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        //设置字体
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 11);//字体大小
        font.setFontName("宋体");//字体
        style.setFont(font);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框

        return style;
    }


    public static HashMap<String, String> makeTitleMap(String title, String key) {
        HashMap<String, String> map = new HashMap<>();
        map.put("title", title);
        map.put("key", key);
        return map;
    }
}
